using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
	public class ServicePriceData
	{
		#region Fields
		protected static SqlConnection dbConn = new SqlConnection(Config.Global.Settings.ConnectionString);
		#endregion

		#region Get Object Method
		private static object getNullObject(object obj)
		{
			return obj == null ? DBNull.Value : obj;
		}
		private static object getNullValue(object obj)
		{
			return obj == DBNull.Value ? null : obj;
		}
		#endregion

		#region Constructors
		public ServicePriceData() {}
		#endregion

		#region Methods

		/// <summary>
		/// Saves a record to the ServicePrice table.
		/// </summary>
		/// <param name="servicePriceInfo"></param>
		/// <returns>ID of Saved record</returns>
		public static int? Insert(ServicePriceInfo servicePriceInfo)
		{
			SqlCommand dbCmd = new SqlCommand("[uspServicePriceInsert]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@ServiceID", getNullObject(servicePriceInfo.ServiceID));
			dbCmd.Parameters.AddWithValue("@ServiceName", getNullObject(servicePriceInfo.ServiceName));
			dbCmd.Parameters.AddWithValue("@ServiceDescription", getNullObject(servicePriceInfo.ServiceDescription));
			dbCmd.Parameters.AddWithValue("@ServicePrice", getNullObject(servicePriceInfo.ServicePrice));
			dbCmd.Parameters.AddWithValue("@ServiceStatus", getNullObject(servicePriceInfo.ServiceStatus));
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value);
			}
			catch (Exception)
			{
				return null;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Updates a record in the ServicePrice table.
		/// </summary>
		/// <param name="servicePriceInfo"></param>
		/// <returns>true: successful; false: unsuccessful</returns>
		public static bool Update(ServicePriceInfo servicePriceInfo)
		{
			SqlCommand dbCmd = new SqlCommand("[uspServicePriceUpdate]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@ServicePriceID", getNullObject(servicePriceInfo.ServicePriceID));
			dbCmd.Parameters.AddWithValue("@ServiceID", getNullObject(servicePriceInfo.ServiceID));
			dbCmd.Parameters.AddWithValue("@ServiceName", getNullObject(servicePriceInfo.ServiceName));
			dbCmd.Parameters.AddWithValue("@ServiceDescription", getNullObject(servicePriceInfo.ServiceDescription));
			dbCmd.Parameters.AddWithValue("@ServicePrice", getNullObject(servicePriceInfo.ServicePrice));
			dbCmd.Parameters.AddWithValue("@ServiceStatus", getNullObject(servicePriceInfo.ServiceStatus));
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value)>0;
			}
			catch (Exception)
			{
				return false;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Deletes a record from the ServicePrice table by its primary key.
		/// </summary>
		/// <param name="servicePriceID"></param>
		/// <returns>true: successful; false: unsuccessful</returns>
		public static bool DeleteByServicePriceID(int servicePriceID)
		{
			SqlCommand dbCmd = new SqlCommand("[uspServicePriceDeleteByServicePriceID]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@ServicePriceID", servicePriceID);
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value)>0;
			}
			catch (Exception)
			{
				return false;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Deletes a record from the ServicePrice table by a foreign key.
		/// </summary>
		/// <param name="serviceID"></param>
		/// <returns>true: successful; false: unsuccessful</returns>
		public static bool DeleteAllByServiceID(int serviceID)
		{
			SqlCommand dbCmd = new SqlCommand("[uspServicePriceDeleteAllByServiceID]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@ServiceID", serviceID);
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value)>0;
			}
			catch (Exception)
			{
				return false;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Selects a single record from the ServicePrice table.
		/// </summary>
		/// <param name="servicePriceID"></param>
		/// <returns>ServicePriceInfo: A record from the ServicePrice table by its primary key.</returns>
		public static ServicePriceInfo SelectByServicePriceID(int servicePriceID)
		{
			SqlCommand dbCmd = new SqlCommand("[uspServicePriceSelectByServicePriceID]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			ServicePriceInfo servicePriceInfo =  new ServicePriceInfo();

			dbCmd.Parameters.AddWithValue("@ServicePriceID", servicePriceID);

			try
			{
				dbConn.Open();
				SqlDataReader dataReader = dbCmd.ExecuteReader();

				if (dataReader.Read())
				{
					servicePriceInfo.ServicePriceID = System.Convert.ToInt32(getNullValue(dataReader["ServicePriceID"]));
					servicePriceInfo.ServiceID = System.Convert.ToInt32(getNullValue(dataReader["ServiceID"]));
					servicePriceInfo.ServiceName = System.Convert.ToString(getNullValue(dataReader["ServiceName"]));
					servicePriceInfo.ServiceDescription = System.Convert.ToString(getNullValue(dataReader["ServiceDescription"]));
					servicePriceInfo.ServicePrice = System.Convert.ToDouble(getNullValue(dataReader["ServicePrice"]));
					servicePriceInfo.ServiceStatus = System.Convert.ToBoolean(getNullValue(dataReader["ServiceStatus"]));
				}

				dataReader.Close();
				return servicePriceInfo;
			}
			catch (Exception)
			{
				return new ServicePriceInfo();
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Selects all records from the ServicePrice table.
		/// </summary>
		/// <return>DataTable</return>
		public static DataTable SelectAll()
		{
			SqlCommand dbCmd = new SqlCommand("[uspServicePriceSelectAll]",dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			DataTable dtbServicePrice =  new DataTable();

			try
			{
				dbConn.Open();
				SqlDataReader dataReader = dbCmd.ExecuteReader();

				if (dataReader.HasRows)
				{
					dtbServicePrice.Load(dataReader);
				}

				dataReader.Close();
				return dtbServicePrice;
			}
			catch (Exception)
			{
				return null;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Selects all records from the ServicePrice table by a foreign key.
		/// </summary>
		/// <param name="serviceID"></param>
		/// <return>All records from the ServicePrice table by a foreign key.</return>
		public static DataTable SelectAllByServiceID(int serviceID)
		{
			SqlCommand dbCmd = new SqlCommand("[uspServicePriceSelectAllByServiceID]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			DataTable dtbServicePrice =  new DataTable();

			dbCmd.Parameters.AddWithValue("@ServiceID", serviceID);

			try
			{
				dbConn.Open();
				SqlDataReader dataReader = dbCmd.ExecuteReader();

				if (dataReader.HasRows)
				{
					dtbServicePrice.Load(dataReader);
				}

				dataReader.Close();
				return dtbServicePrice;
			}
			catch (Exception)
			{
				return null;
			}
			finally
			{
				dbConn.Close();
			}
		}

		#endregion
	}
}
